package com.vortexinfo.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;



import jxl.Cell;
import jxl.DateCell;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.biff.EmptyCell;
import jxl.read.biff.BiffException;

/**
 * @author flyoung
 * 
 */
public class JXLUtil {
	private final static String EMPTY = "";
	private Workbook workbook = null; // 工作部对象
	private InputStream inputStream = null;
	private HashMap<String, String> mapData = null; // data数据

	private Sheet sheet = null; // 工作表

	public int totalRows = 0; // 总行数

	public int totalCells = 0; // 总列数

	/**
	 * 以一个InputStream为参数的构造器
	 * 
	 * @param inputStream
	 * @throws IOException
	 * @throws BiffException
	 */
	public JXLUtil(InputStream inputStream) throws BiffException, IOException {
		this.inputStream = inputStream;
		this.workbook = Workbook.getWorkbook(inputStream);
		this.sheet = this.workbook.getSheet(0);
		this.getRows();
		this.getCells();
	}

	/**
	 * 以一个File为参数的构造器
	 * 
	 * @param file
	 * @throws IOException
	 * @throws BiffException
	 */
	public JXLUtil(File file) throws BiffException, IOException {
		this(new FileInputStream(file));
	}

	public void close() throws IOException {
		this.workbook.close();
		this.inputStream.close();
	}

	/**
	 * 以一个文件路径path的构造器
	 * 
	 * @param filePath
	 * @throws IOException
	 * @throws BiffException
	 */
	public JXLUtil(String filePath) throws BiffException, IOException {

		this(new File(filePath));
	}

	/**
	 * 把所有数据放到一个map中去,key为行号加列号
	 * 
	 * @return
	 */
	public HashMap<String, String> getExcelData() {
		mapData = new HashMap<String, String>();
		for (int i = 0; i < this.totalRows; i++) {
			for (int j = 0; j < this.totalCells; j++) {
				this.mapData.put(i + "" + j, this.getData(j, i));
			}
		}
		return this.mapData;
	}

	/**
	 * 得到总行数
	 */
	public int getRows() {
		this.totalRows = sheet.getRows();
		return this.totalRows;
	}

	/**
	 * 得到总列数
	 */
	public int getCells() {
		this.totalCells = sheet.getColumns();
		return totalCells;
	}

	/**
	 * 得到数据
	 * 
	 * @param cell
	 * @param row
	 * @return
	 */
	public String getData(int cell, int row) {
		Cell rs = this.sheet.getCell(cell, row);
		if (rs instanceof EmptyCell) {
			return null;
		}
		return rs.getContents().toString().trim();
	}

	/**
	 * 得到数据
	 * 
	 * @param cell
	 * @param row
	 * @return
	 */
	public String getData(int column, int row, String columnType) {
		Cell cell = this.sheet.getCell(column, row);
		if (cell instanceof EmptyCell) {
			return null;
		}
		if ("NUMBER".equals(columnType)) {
			if (!StringUtil.isNullOrEmpty(cell.getContents())) {
				try {
					NumberCell numberCell = (NumberCell) cell;
					double value = numberCell.getValue();
					return value + EMPTY;
				} catch (Exception e) {
					e.printStackTrace();
				}

			}
			return EMPTY;
		} else if ("DATE".equals(columnType)) {
			if (!StringUtil.isNullOrEmpty(cell.getContents())) {
				try {
					DateCell dateCell = (DateCell) cell;
					Date date = dateCell.getDate();

					return DateUtil.format(date, DateUtil.DATE_FORMAT);
				} catch (Exception e) {
					e.printStackTrace();
				}

			}
			return EMPTY;
		} else if ("DATETIME".equals(columnType)) {
			if (!StringUtil.isNullOrEmpty(cell.getContents())) {
				try {
					DateCell dateCell = (DateCell) cell;
					Date date = dateCell.getDate();

					Calendar c = Calendar.getInstance();
					c.setTime(date);
					c.set(Calendar.HOUR_OF_DAY, c.get(Calendar.HOUR_OF_DAY) - 8);
					return DateUtil.format(c.getTime(),
							DateUtil.DATETIME_FORMAT);
				} catch (Exception e) {
					e.printStackTrace();
				}

			}
			return EMPTY;

		} else {
			return StringUtil.trim(cell.getContents());
		}

	}

	public static void main(String[] args) throws Exception {
		JXLUtil excelUtil = new JXLUtil(new File(
				"C:\\Users\\dejunx\\Desktop\\szcc-company\\苏B99585 整理 没图片.xls"));
		int rowNum = excelUtil.totalRows;
		int columnNum = excelUtil.totalCells;
		System.out.println("rowNum=" + rowNum + ",columnNum=" + columnNum);
		for (int i = 1; i < rowNum; i++) {
			for (int j = 0; j < columnNum; j++) {
				if (j == 19) {
					System.out.print(excelUtil.getData(j, i, "DATETIME") + ",");
				} else if (j == 18) {
					System.out.print(excelUtil.getData(j, i, "DATE") + ",");
				} else if (j == 16) {
					System.out.print(excelUtil.getData(j, i, "NUMBER") + ",");
				} else if (j == 15) {
					System.out.print(excelUtil.getData(j, i, "NUMBER") + ",");
				} else {
					System.out.print(excelUtil.getData(j, i, "NULL") + ",");
				}

			}
			System.out.println("");
		}
		excelUtil.close();

	}
}